package Dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import Entity.CourseResource;
import OracleHelper.OracleHelper;


public class CourseResourceDao {
	
	/*向courseResource表中插入数据*/
	public int addCourseResource(CourseResource cr) {
		String sql="insert into courseResource values('"+cr.getCourseID()+"','"+cr.getLabel()+"',"
				+ "'"+cr.getCourseName()+"','"+cr.getUploaderID()+"','"+cr.getUploadTime()+"','"+cr.getFilePath()+"','"+cr.getBriefIntroduce()+"')";
		return new OracleHelper().executeUpdate(sql);
		
	}
	
	/*删除一条信息*/
	public int deleteCourseResourceById(String courseID) {
		String sql="delete from courseResource where courseID='"+courseID+"'";
		return new OracleHelper().executeUpdate(sql);
		
	}
	
	/*通过课程号查询某一课程所有信息*/
	public CourseResource queryCourseResourceByCourseID(String courseID){
		CourseResource cr=new CourseResource();
		String sql="select * from courseResource where courseID='"+courseID+"'";
		ResultSet rs=OracleHelper.executeQuery(sql);
		try{
			while(rs.next()){
				cr.setCourseID(rs.getString(1).toString());
				cr.setLabel(rs.getString(2).toString());
				cr.setCourseName(rs.getString(3).toString());
				cr.setUploaderID(rs.getString(4).toString());
				cr.setUploadTime(rs.getString(5));
				cr.setFilePath(rs.getString(6).toString());
				cr.setBriefIntroduce(rs.getString(7).toString());				
			}
			OracleHelper.closeConnection();;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return cr;		
	}
	
	/*通过标签查询所有相关课程*/
	public List<CourseResource> queryCourseResourceByLabel(String label){
		List<CourseResource> crlist=new ArrayList<CourseResource>();
		String sql="select * from courseResource where label='"+label+"'";
		ResultSet rs=OracleHelper.executeQuery(sql);
		try{
			while(rs.next()){
				CourseResource cr=new CourseResource();
				cr.setCourseID(rs.getString(1).toString());
				cr.setLabel(rs.getString(2).toString());
				cr.setCourseName(rs.getString(3).toString());
				cr.setUploaderID(rs.getString(4).toString());
				cr.setUploadTime(rs.getString(5));
				cr.setFilePath(rs.getString(6).toString());
				cr.setBriefIntroduce(rs.getString(7).toString());
				crlist.add(cr);
			}
			OracleHelper.closeConnection();;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return crlist;		
	}
	
	/*通过上传者的ID查询该上传者上传的所有课程信息*/
	public List<CourseResource> queryCourseResourceByUploaderID(String uploaderID){
		List<CourseResource> crlist=new ArrayList<CourseResource>();
		String sql="select * from courseResource where uploaderID='"+uploaderID+"'";
		ResultSet rs=OracleHelper.executeQuery(sql);
		try{
			while(rs.next()){
				CourseResource cr=new CourseResource();
				cr.setCourseID(rs.getString(1).toString());
				cr.setLabel(rs.getString(2).toString());
				cr.setCourseName(rs.getString(3).toString());
				cr.setUploaderID(rs.getString(4).toString());
				cr.setUploadTime(rs.getString(5).toString());
				cr.setFilePath(rs.getString(6).toString());
				cr.setBriefIntroduce(rs.getString(7).toString());
				crlist.add(cr);
			}
			OracleHelper.closeConnection();;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return crlist;			
	}
	
	/*显示所有课程信息*/
	public List<CourseResource> showAllCourses(){
		List<CourseResource> crlist=new ArrayList<CourseResource>();
		String sql="select * from courseResource ";
		ResultSet rs=OracleHelper.executeQuery(sql);
		try{
			while(rs.next()){
				CourseResource cr=new CourseResource();
				cr.setCourseID(rs.getString(1).toString());
				cr.setLabel(rs.getString(2).toString());
				cr.setCourseName(rs.getString(3).toString());
				cr.setUploaderID(rs.getString(4).toString());
				cr.setUploadTime(rs.getString(5).toString());
				cr.setFilePath(rs.getString(6).toString());
				cr.setBriefIntroduce(rs.getString(7).toString());
				crlist.add(cr);
			}
			OracleHelper.closeConnection();;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return crlist;			
	}
	
	/*获取一共有多少条信息*/
	public int queryRecordCount() {
		String sql = "select count(*) from courseResource";
		Object obj = new OracleHelper().executeSingleQuery(sql);
		return Integer.parseInt(obj.toString());
	}

}
